﻿using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class T_ProjectRepository : BaseRepository
    {
        public PagedList<T_Investment_EX> QueryInvestmentList(int? projectid, int? status, ProjectSearch search, PageView view)
        {
            string cols = @"A.Status,C.Name AS UserName,B.ProjectNo,B.ProjectName,A.Id,A.TenderAmt,A.CreateTime ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND B.ProjectName LIKE '%" + search.Name + "%'";
            }
            if (projectid != null)
            {
                sqlWhere += " AND A.ProjectId =  " + projectid.ToString() + " ";

            }
            if (status != null)
            {
                sqlWhere += " AND A.Status =  " + status.ToString() + " ";
            }

            return base.PageGet<T_Investment_EX>(view, cols,
                @"T_Investment A LEFT JOIN T_Project B ON A.ProjectId=B.ID
                LEFT JOIN U_Member C ON A.MemberId=C.ID "
                , sqlWhere
                , "A.ID DESC ",
                "");
        }

        public PagedList<U_Company> QueryCompanyList(CompanySearch search, PageView view)
        {
            string cols = @" [Id]
                            ,[MemberId]
                            ,[CompanyName]
                            ,[Adress]
                            ,[Postcode]
                            ,[Faxes]
                            ,[Employees]
                            ,[Industry]
                            ,[Nature]
                            ,[Synopsis]
                            ,[Constitution]
                            ,[Status] ";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.CompanyName))
            {
                sqlWhere += " AND [CompanyName] LIKE '%" + search.CompanyName + "%'";
            }
            return base.PageGet<U_Company>(view, cols,
                @" [U_Company] "
                , sqlWhere
                , " [ID] DESC ",
                "");
        }

        /// <summary>
        /// 获取项目列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="projectTime"></param>
        /// <param name="AnnualisedRates"></param>
        /// <param name="RepaymentStatus"></param>
        /// <param name="projectStatus"></param>
        /// <returns></returns>
        public List<T_Project_EX> GetProjectListNew(int? pageIndex, int pageSize, string projectTime, string AnnualisedRates, string projectStatus)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(@" 
                SELECT T.* FROM(
	SELECT  ROW_NUMBER() over(order by B.ProjectStatus ASC,B.[ID] DESC) row,B.* FROM(
		SELECT  A.[ID]
		,A.[ProjectNo]
		,A.[ProjectName]
		,CONVERT(DECIMAL(18,2),(A.[TotalLoan])) AS TotalLoan
		,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
		,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 )) as TenderAmt
		,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 ))
			/(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
		,A.[AnnualisedRates]*100 AS AnnualisedRates
		,A.[AddAnnualisedRates]*100 AS AddAnnualisedRates
		,A.[EnterpriseID]
		,B.EnterpriseName
		,A.[ProjectBeginDate]
		,A.[LoanType]
		,A.[AuditStatus]
		,A.[LoanStatus]
		,A.[RepaymentStatus]
		,A.TenderStatus
		,A.[ProjectDuration]
		,A.[DurationMonthOrDay]
        ,case 
				when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 1
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 2
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 3
                when A.RepaymentStatus = 1 
                then 4
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 5  
                
                else 6
		end as ProjectStatus 
        FROM [T_Project] A  LEFT JOIN T_Enterprise B ON A.EnterpriseID=B.ID  WHERE A.[AuditStatus]=2 and A.SubProject=0
        union
        
        select  
        A.[ID]
		,A.[ProjectNo]
		,A.[ProjectName]
		,CONVERT(DECIMAL(18,2),(A.[TotalLoan])) AS TotalLoan
		,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
		,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 )) as TenderAmt
		,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 ))
			/(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
		,A.[AnnualisedRates]*100 AS AnnualisedRates
		,A.[AddAnnualisedRates]*100 AS AddAnnualisedRates
		,A.[EnterpriseID]
		,B.EnterpriseName
		,A.[ProjectBeginDate]
		,A.[LoanType]
		,A.[AuditStatus]
		,A.[LoanStatus]
		,A.[RepaymentStatus]
		,A.TenderStatus
		,A.[ProjectDuration]
		,A.[DurationMonthOrDay]
        ,case 
				when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 1
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 2
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 3
                when A.RepaymentStatus = 1 
                then 4
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 5  
                
                else 6
		end as ProjectStatus
        FROM [T_Project] A  LEFT JOIN T_Enterprise B ON A.EnterpriseID=B.ID
		where A.ID in (
			select  MIN(id)  from T_Project  
			where   SubProject=1  and   ParentProjectID is not null and TenderStatus=0 
			group by  ParentProjectID
            )		

            ");



            StringBuilder sbwhere = new StringBuilder();
            if (!string.IsNullOrEmpty(projectTime))
            {
                switch (projectTime)
                {
                    case "0to3":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration<=3 ");
                        break;
                    case "3to6":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration>=3  AND ProjectDuration<=6  ");
                        break;
                    case "6to9":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration>=6  AND ProjectDuration<=9  ");
                        break;
                    case "9":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration>9 ");
                        break;
                    case "t":
                        sbwhere.Append(" AND DurationMonthOrDay=2 ");
                        break;
                    default:
                        sbwhere.Append("");
                        break;
                }
            }

            if (!string.IsNullOrEmpty(AnnualisedRates))
            {
                switch (AnnualisedRates)
                {
                    case "to9":
                        sbwhere.Append(" AND A.AnnualisedRates*100 < 9 ");
                        break;
                    case "9to11":
                        sbwhere.Append(" AND A.AnnualisedRates*100 >= 9 AND A.AnnualisedRates*100 <= 11 ");
                        break;
                    case "11to13":
                        sbwhere.Append(" AND A.AnnualisedRates*100 >= 11 AND A.AnnualisedRates*100 <= 13 ");
                        break;
                    case "13":
                        sbwhere.Append(" AND A.AnnualisedRates*100 > 13 ");
                        break;
                    default:
                        sbwhere.Append("");
                        break;
                }
            }

            sb.Append(sbwhere);
            sb.Append(" )B ");
            if (!string.IsNullOrEmpty(projectStatus))
            {
                sb.Append(" where b.ProjectStatus=" + projectStatus);
            }
            sb.Append(" )T  where t.row between " + pageSize + "*(" + pageIndex + "-1)+1 and  " + pageSize + "*(" + pageIndex + "-1)+" + pageSize);

            return base.Query<T_Project_EX>(sb.ToString(), null);
        }

        public int GetProjectListNewCount(int? pageIndex, int pageSize, string projectTime, string AnnualisedRates, string projectStatus)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(@" SELECT count([ID]) 
                          FROM 
                            (SELECT  A.[ID]
                          ,A.[ProjectNo]
                          ,A.[ProjectName]
                          ,CONVERT(DECIMAL(18,2),(A.[TotalLoan]/10000)) AS TotalLoan
                         ,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
                          ,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID )) as TenderAmt
						  ,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID ))
							/(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
                          ,A.[AnnualisedRates]*100 AS AnnualisedRates
                          ,A.[AddAnnualisedRates]*100 AS AddAnnualisedRates
                          ,A.[EnterpriseID]
                          ,B.EnterpriseName
                          ,A.[ProjectBeginDate]
                          ,A.[LoanType]
                          ,A.[AuditStatus]
                          ,A.[LoanStatus]
                          ,A.[RepaymentStatus]
                          ,A.TenderStatus
                          ,A.[ProjectDuration]
                          ,A.[DurationMonthOrDay]
                          

                ,case 
              	when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 1
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 2
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 3
                when A.RepaymentStatus = 1 
                then 4
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 5  
                
                else 6
                end as ProjectStatus from [T_Project] A  LEFT JOIN T_Enterprise B ON A.EnterpriseID=B.ID  WHERE A.[AuditStatus]=2 ");

            StringBuilder sbwhere = new StringBuilder();
            if (!string.IsNullOrEmpty(projectTime))
            {
                switch (projectTime)
                {
                    case "0to3":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration<=3 ");
                        break;
                    case "3to6":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration>=3  AND ProjectDuration<=6  ");
                        break;
                    case "6to9":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration>=6  AND ProjectDuration<=9  ");
                        break;
                    case "9":
                        sbwhere.Append(" AND DurationMonthOrDay=1 AND ProjectDuration>9 ");
                        break;
                    case "t":
                        sbwhere.Append(" AND DurationMonthOrDay=2 ");
                        break;
                    default:
                        sbwhere.Append("");
                        break;
                }
            }

            if (!string.IsNullOrEmpty(AnnualisedRates))
            {
                switch (AnnualisedRates)
                {
                    case "to9":
                        sbwhere.Append(" AND A.AnnualisedRates*100 < 9 ");
                        break;
                    case "9to11":
                        sbwhere.Append(" AND A.AnnualisedRates*100 >= 9 AND A.AnnualisedRates*100 <= 11 ");
                        break;
                    case "11to13":
                        sbwhere.Append(" AND A.AnnualisedRates*100 >= 11 AND A.AnnualisedRates*100 <= 13 ");
                        break;
                    case "13":
                        sbwhere.Append(" AND A.AnnualisedRates*100 > 13 ");
                        break;
                    default:
                        sbwhere.Append("");
                        break;
                }
            }

            sb.Append(sbwhere);
            sb.Append(") c");
            if (!string.IsNullOrEmpty(projectStatus))
            {
                sb.Append(" where ProjectStatus=" + projectStatus);

            }
            return base.Get<int>(sb.ToString(), null);
        }

        public double getRemainingPrincipal(int? id)
        {
            string sql = @" SELECT [RemainingPrincipal] FROM [T_RepaymentPlan]
                          WHERE ID=@ID";
            return base.Get<double>(sql, new { ID = id });
        }

        public List<T_Project> GetProjectList()
        {
            string sql = string.Empty;
            sql = @" SELECT TOP(3)              [ID]
                           ,[ProjectNo]
                           ,[ProjectName]
                           ,[ProjectContent]
                           ,[TotalLoan]
                           ,[AnnualisedRates]*100 AS AnnualisedRates
                           ,[EnterpriseID]
                           ,[EnterpriseAgent]
                           ,[ProjectLocation]
                           ,[RepaymentPlanDate]
                           ,[LoanPurpose]
                           ,[RepaymentSource]
                           ,[LoanType]
                           ,[AuditStatus]
                           ,[LoanStatus]
                           ,[RepaymentStatus]
                           ,[CreateTime]
                           ,[CreateUser]
                           ,[AuditTime]
                           ,[AuditUser]
                           ,[LoanTime]
                           ,[LoanUser]
                           ,[RepaymentTime]
                            ,TenderStatus
                           ,[RepaymentUser] FROM [T_Project] ORDER BY TenderStatus ASC,ID DESC";
            return base.Query<T_Project>(sql, null);
        }

        public List<T_Project> GetProjectList(int? id)
        {
            string sql = string.Empty;
            sql = @" SELECT  [ID]
                              ,[ProjectNo]
                              ,[ProjectName]
                              ,[ProjectContent]
                              ,[TotalLoan]
                              ,[AnnualisedRates]
                              ,[EnterpriseID]
                              ,[EnterpriseAgent]
                              ,[ProjectLocation]
                              ,[RepaymentPlanDate]
                              ,[LoanPurpose]
                              ,[RepaymentSource]
                              ,[LoanType]
                              ,[AuditStatus]
                              ,[LoanStatus]
                              ,[RepaymentStatus]
                              ,[CreateTime]
                              ,[CreateUser]
                              ,[AuditTime]
                              ,[AuditUser]
                              ,[LoanTime]
                              ,[LoanUser]
                              ,[RepaymentTime]
                              ,[RepaymentUser]
                              ,[ProjectDuration]
                               ,[TenderStatus]
                              ,[DurationMonthOrDay] FROM [T_Project] WHERE ID=@ID  ORDER BY TenderStatus ASC,ID DESC ";
            return base.Query<T_Project>(sql, new { ID = id });
        }
        /// <summary>
        /// 首页最新投资项目列表
        /// </summary>
        /// <returns></returns>
        public List<T_Project_EX> GetNewProjectList(string isNewProject, int top)
        {
            string sql = string.Empty;
            // ,CONVERT(DECIMAL(18,2),(A.[TotalLoan])) AS TotalLoan
            sql = string.Format(@" 
                SELECT top {0}  A.[ID]
                ,A.[ProjectNo]
                ,A.[ProjectName]
                ,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
                ,CONVERT(DECIMAL(18,2),(A.[TotalLoan]/10000)) AS TotalLoan
                ,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 )) as TenderAmt
                ,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 ))
                /(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
               ,CONVERT(DECIMAL(18,1),A.[AnnualisedRates]*100) AS AnnualisedRates
                ,CONVERT(DECIMAL(18,1),A.[AddAnnualisedRates]*100) AS AddAnnualisedRates
                ,A.[EnterpriseID]
                ,B.EnterpriseName
                ,A.[ProjectBeginDate]
                ,A.[LoanType]
                ,A.[AuditStatus]
                ,A.[LoanStatus]
                ,A.[RepaymentStatus]
                ,A.TenderStatus
                ,A.[ProjectDuration]
                ,A.[DurationMonthOrDay]
                          
                ,case 
               	when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 1
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 2
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 3
                when A.RepaymentStatus = 1 
                then 4
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 5  
                
                else 6
                end as ProjectStatus 

                FROM [T_Project] A  LEFT JOIN T_Enterprise B ON A.EnterpriseID=B.ID  WHERE A.[AuditStatus]=2", top);

            if (isNewProject.Equals("true"))
            {
                //是新手项目
                sql += " AND IsNewProject=1 ";
            }

            sql += "order by ProjectStatus ASC,a.id desc  ";

            return base.Query<T_Project_EX>(sql, null);
        }

        public PagedList<T_Project> QueryProjectListPage(PageView view, int pageSize, int? currentPage)
        {
            string cols = " TOP " + pageSize + @" [ID]
                        ,[ProjectNo]
                        ,[ProjectName]
                        ,[ProjectContent]
                        ,[TotalLoan]
                        ,[AnnualisedRates]
                        ,[EnterpriseID]
                        ,[EnterpriseAgent]
                        ,[ProjectLocation]
                        ,[RepaymentPlanDate]
                        ,[LoanPurpose]
                        ,[RepaymentSource]
                        ,[LoanType]
                        ,[AuditStatus]
                        ,[LoanStatus]
                        ,[RepaymentStatus]
                        ,[CreateTime]
                        ,[CreateUser]
                        ,[AuditTime]
                        ,[AuditUser]
                        ,[LoanTime]
                        ,[LoanUser]
                        ,[RepaymentTime]
                        ,[RepaymentUser],[TenderStatus]
                        ,[DurationMonthOrDay]";
            string sqlWhere = "";
            if (currentPage.HasValue)
            {
                if (currentPage != 1)
                {
                    sqlWhere += " AND [ID]>(SELECT MAX([ID]) FROM (SELECT TOP " + (currentPage - 1) * pageSize + " [ID] FROM [T_Project] ORDER BY TenderStatus ASC,ID DESC)a)";
                }
            }

            return base.PageGet<T_Project>(view, cols,
                @" [T_Project] "
                , sqlWhere
                , " TenderStatus ASC,ID DESC ",
                "");
        }

        /// <summary>
        /// 删除项目信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteInvestment(int id)
        {
            string sql = " DELETE FROM [T_Project] WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }

        public string GetProjectNo(int loanType)
        {
            string temp = "";
            switch (loanType)
            {
                case 0: temp = "QJZQH"; break;
                case 1: temp = "QJZQC"; break;
                case 2: temp = "QJZQN"; break;
            }
            string sql = @"SELECT TOP 1 ProjectNo FROM [T_Project] 
                         WHERE SUBSTRING(ProjectNo,0,14)='" + temp + "'+CONVERT(varchar(100), GETDATE(), 112) AND [ParentProjectID] IS NULL  ORDER BY RIGHT(ProjectNo,3) DESC";
            return base.Get<string>(sql, null);
        }

        /// <summary>
        /// 删除会员投资信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteProject(int id)
        {
            string sql = " DELETE FROM T_Investment WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }

        public int DeleteRelement(int id)
        {
            string sql = " DELETE FROM [T_RelevantDocument] WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }

        public int DeleteRelementByPid(int projectID)
        {
            string sql = " DELETE FROM [T_RelevantDocument] WHERE ProjectID=@ProjectID";
            return base.Excute(sql, new { ProjectID = projectID });
        }

        /// <summary>
        /// 检索投资信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T_Investment GetInvesment(int? id)
        {
            string sql = @" SELECT [Id]
                      ,[ProjectId]
                      ,[MemberId]
                      ,[TenderAmt]
                      ,[CreateTime]
                      ,[CreateUser]
                      ,[Status]
                       FROM [T_Investment] WHERE Id=@ID";
            return base.Get<T_Investment>(sql, new { ID = id });
        }

        /// <summary>
        /// 获取债权列表
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<T_Project_EX> QueryProjectList(T_ProjectSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[ProjectNo]
                          ,[ProjectName]
                          ,[ProjectContent]
                          ,[TotalLoan]
                          ,[AnnualisedRates]
                          ,[AddAnnualisedRates]
                          ,[EnterpriseID]
                          ,[EnterpriseAgent]
                          ,[ProjectLocation]
                          ,[RepaymentPlanDate]
                          ,[LoanPurpose]
                          ,[RepaymentSource]
                          ,[LoanType]
                          ,[AuditStatus]
                          ,[LoanStatus]
                          ,[RepaymentStatus]
                          ,[CreateTime]
                          ,[CreateUser]
                          ,[AuditTime]
                          ,[AuditUser]
                          ,[LoanTime]
                          ,[LoanUser]
                          ,[RepaymentTime]
                          ,[RepaymentUser]
                          ,[AuditExplain]
                          ,[TenderStatus]
                          ,[SubProject]
                          ,[EachSubProjectAmount]
                          ,[ParentProjectID]
                          ,[MinTenderAmount]
,(SELECT sum([TenderAmt]) FROM [T_Investment] where ProjectId = T_Project.ID and status = 1 ) as TenderAmt ";
            string sqlWhere = " AND [ParentProjectID] IS NULL";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND [ProjectName] LIKE '%" + search.Name + "%'";
            }

            return base.PageGet<T_Project_EX>(view, cols,
                @" [T_Project] "
                , sqlWhere
                , " [ID] DESC ",
                "");
        }
        //查询子债权项目列表？
        public PagedList<T_Project> QueryProjectList(int? id, ProjectSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[ProjectNo]
                          ,[ProjectName]
                          ,[ProjectContent]
                          ,[TotalLoan]
                          ,[AnnualisedRates]
                          ,[EnterpriseID]
                          ,[EnterpriseAgent]
                          ,[ProjectLocation]
                          ,[RepaymentPlanDate]
                          ,[LoanPurpose]
                          ,[RepaymentSource]
                          ,[LoanType]
                          ,[AuditStatus]
                          ,[LoanStatus]
                          ,[RepaymentStatus]
                          ,[CreateTime]
                          ,[CreateUser]
                          ,[AuditTime]
                          ,[AuditUser]
                          ,[LoanTime]
                          ,[LoanUser]
                          ,[RepaymentTime]
                          ,[RepaymentUser] 
                          ,[EachSubProjectAmount]";
            string sqlWhere = " AND [ParentProjectID]=" + id;
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND [ProjectName] LIKE '%" + search.Name + "%'";
            }

            return base.PageGet<T_Project>(view, cols,
                @" [T_Project] "
                , sqlWhere
                , " TenderStatus ASC,ID DESC ",
                "");
        }

        public List<T_RepaymentPlan_EX> GetRPListByProID(int id)
        {
            string sql = @" select  r.[ID]
                          ,[RepaymentPlanNO]
                          ,[RepaymentPlanDate]
                          ,TotalLoan
                          ,ISNULL(CONVERT(char(10),RepaymentPlanDate,120),'完标生成') AS RepaymentPlanDateStr 
                          ,[CurrentPrincipal]
                          ,[RemainingPrincipal]
                          ,CASE [Status] WHEN 1 THEN '待还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,[ProjectID]
                          from T_RepaymentPlan r Left Join (select ID,TotalLoan from T_Project)p ON r.[ProjectID]=p.[ID] where ProjectID=@ProjectID";
            return base.Query<T_RepaymentPlan_EX>(sql, new { ProjectID = id });
        }


        public PagedList<T_RepaymentPlan_EX> QueryRepaymentPlanList(int? id, ProjectSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[RepaymentPlanNO]
                          ,[RepaymentPlanDate]
                          ,ISNULL(CONVERT(char(10),RepaymentPlanDate,120),'完标生成') AS RepaymentPlanDateStr 
                          ,[CurrentPrincipal]
                          ,[RemainingPrincipal]
                          ,CASE [Status] WHEN 1 THEN '待还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,[ProjectID] ";
            string sqlWhere = " AND [ProjectID]=" + id;
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND [RepaymentPlanNO] LIKE '%" + search.Name + "%'";
            }

            return base.PageGet<T_RepaymentPlan_EX>(view, cols,
                @" [T_RepaymentPlan] "
                , sqlWhere
                , " [ID] ",
                "");
        }

        public PagedList<T_RepaymentPlan_EX> QueryRepaymentPlanList(ProjectSearch search, PageView view)
        {
            string cols = @" r.[ID]
                          ,r.[RepaymentPlanNO]
                          ,r.[RepaymentPlanDate]
                          ,r.[CurrentPrincipal]
                          ,r.[RemainingPrincipal]
                          ,CASE r.[Status] WHEN 1 THEN '待还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,r.[ProjectID] 
                          ,p.[ID] AS pid
                          ,p.[ProjectNo]
                          ,p.[ProjectName]
                          ,p.[TotalLoan]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND p.[ProjectName] LIKE '%" + search.Name + "%' ";
            }

            //sqlWhere += " AND (r.RepaymentPlanDate < GETDATE()) ";


            return base.PageGet<T_RepaymentPlan_EX>(view, cols,
                @" [T_RepaymentPlan] r Left Join [T_Project] p ON r.[ProjectID]=p.[ID] "
                , sqlWhere
                //, " p.[ID] DESC ",
                , " r.[RepaymentPlanDate]  ",
                "");
        }



        public PagedList<T_RepaymentPlan_EX> QueryToBeRepaymentPlanList(ProjectSearch search, PageView view)
        {
            string cols = @" r.[ID]
                          ,r.[RepaymentPlanNO]
                          ,r.[RepaymentPlanDate]
                          ,r.[CurrentPrincipal]
                          ,r.[RemainingPrincipal]
                          ,CASE r.[Status] WHEN 1 THEN '待还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,r.[ProjectID] 
                          ,p.[ID] AS pid
                          ,p.[ProjectNo]
                          ,p.[ProjectName]
                          ,p.[TotalLoan]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND p.[ProjectName] LIKE '%" + search.Name + "%' ";
            }

            sqlWhere += " AND (r.RepaymentPlanDate < GETDATE()) and r.[Status]<>3 ";

            return base.PageGet<T_RepaymentPlan_EX>(view, cols,
                @" [T_RepaymentPlan] r Left Join [T_Project] p ON r.[ProjectID]=p.[ID] "
                , sqlWhere
                , " p.[ID] DESC ",
                "");
        }



        public PagedList<T_RepaymentPlan_EX> QueryToBeRepaymentDetailList(ProjectSearch search, PageView view)
        {
            string cols = @" r.[ID]
                          ,r.[RepaymentPlanNO]
                          ,r.[RepaymentPlanDate]
                          ,r.[CurrentPrincipal]
                          ,r.[RemainingPrincipal]
                          ,CASE r.[Status] WHEN 1 THEN '待还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,r.[ProjectID] 
                          ,p.[ID] AS pid
                          ,p.[ProjectNo]
                          ,p.[ProjectName]
                          ,p.[TotalLoan]";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND p.[ProjectName] LIKE '%" + search.Name + "%' ";
            }

            sqlWhere += " AND (r.RepaymentPlanDate < GETDATE()) and r.[Status]<>3 ";

            return base.PageGet<T_RepaymentPlan_EX>(view, cols,
                @" [T_RepaymentPlan] r Left Join [T_Project] p ON r.[ProjectID]=p.[ID] "
                , sqlWhere
                , " p.[ID] DESC ",
                "");
        }

        public int SaveRepaymentPlan(int? id, string RepaymentPlanNO, double? CurrentPrincipal, decimal? TotalLoan)
        {
            string sql = " INSERT INTO [T_RepaymentPlan](RepaymentPlanNO,CurrentPrincipal,RemainingPrincipal,Status,ProjectID) VALUES(@RepaymentPlanNO,@CurrentPrincipal,@TotalLoan,1,@ID) ";
            return base.Excute(sql, new { RepaymentPlanNO = RepaymentPlanNO, CurrentPrincipal = Math.Round(Convert.ToDouble(CurrentPrincipal), 2), TotalLoan = TotalLoan, ID = id });
        }

        public string RepaymentPlanNO()
        {
            string sql = @" SELECT TOP 1 [RepaymentPlanNO] FROM [T_RepaymentPlan]
                          WHERE SUBSTRING([RepaymentPlanNO],0,9)=CONVERT(varchar(100), GETDATE(), 112) ORDER BY RIGHT([RepaymentPlanNO],3) DESC";
            return base.Get<string>(sql, null);
        }

        public int ChangeLoan(int? id, string name)
        {
            string sql = " UPDATE [T_Project] SET LoanStatus=1,LoanUser=@name,LoanTime=GETDATE() WHERE ID=@ID";
            return base.Excute(sql, new { ID = id, name = name });
        }
        //将还款计划表的状态改为3，就表示已完成
        public int RepaymentPlan(int? id)
        {
            string sql = " UPDATE [T_RepaymentPlan] SET [Status]=3 WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }

        public int GetRepmStatus(int? id)
        {
            string sql = @" SELECT [Status] FROM [T_RepaymentPlan]
                          WHERE ID=@ID";
            return base.Get<int>(sql, new { ID = id });
        }

        public int ChangeRept(int? id, string name)
        {
            string sql = " UPDATE [T_Project] SET RepaymentStatus=1,RepaymentUser=@name ,RepaymentTime=GETDATE() WHERE ID=@ID";
            return base.Excute(sql, new { ID = id, name = name });
        }

        public int ChangeAudit(int? id, string name, int? status, string Audit)
        {
            string sql = " UPDATE [T_Project] SET AuditTime=GETDATE(),AuditStatus=@status,AuditUser=@name,AuditExplain=@Audit WHERE ID=@ID";
            return base.Excute(sql, new { ID = id, name = name, status = status, Audit = Audit });
        }

        public List<T_FundsFlow> getFundsList(int? id)
        {
            string sql = @" SELECT [ID],[MemberId],[OutPay] FROM T_FundsFlow WHERE [ProjectId]=@ID AND [Type]=3";
            return base.Query<T_FundsFlow>(sql, new { ID = id });
        }

        public int SaveRept(int? projectid, string name, int? id, int? MemberId, double? OutPay)
        {
            string sql = @" INSERT INTO [T_FundsFlow]([MemberId]
                          ,[InCome]
                          ,[CreateTime]
                          ,[Type]
                          ,[ProjectId]
                          ,[ParentId],[Status]) VALUES(@MemberId,@OutPay,GETDATE(),4,@projectid,@ID,1) ";
            return base.Excute(sql, new { MemberId = MemberId, OutPay = OutPay, projectid = projectid, ID = id });
        }


        //获取项目信息
        public T_Project_EX GetProject(int? id)
        {
            string sql = @" SELECT p.[ID]
                        ,p.[ProjectNo]
                        ,p.[ProjectName]
                        ,p.[ProjectContent]
                        ,p.[TotalLoan]       
                        ,p.[AnnualisedRates]
                        ,p.[AddAnnualisedRates]
                        ,p.[EnterpriseID]
                        ,p.[EnterpriseAgent]
                        ,p.[ProjectLocation]
                        ,p.[RepaymentPlanDate]
                        ,p.[LoanPurpose]
                        ,p.[RepaymentSource]
                        ,p.[RepaymentType]
                        ,p.[LoanType]
                        ,p.[AuditStatus]
                        ,p.[TenderStatus]
                        ,CASE p.[LoanStatus] WHEN 0 THEN '未借款' WHEN 1 THEN '已借款' END AS LoanStatu
                        ,CASE p.[RepaymentStatus] WHEN 0 THEN '未还款' WHEN 1 THEN '已还款' END AS RepaymentStatu
                        ,p.[CreateTime]
                        ,p.[CreateUser]
                        ,p.[AuditTime]
                        ,p.[AuditUser]
                        ,p.[LoanTime]
                        ,p.[LoanUser]
                        ,p.[RepaymentTime]
                        ,p.[RepaymentUser]
                        ,p.[ProjectDuration]
                        ,p.[DurationMonthOrDay]
                        ,p.AuditExplain
                        ,p.ProjectBeginDate
                        ,p.ProjectEndDate
                        ,p.[ProjectDetail]
                        ,p.[IsNewProject]
                        ,[TenderStatus]
                        ,[SubProject]
                        ,[EachSubProjectAmount]
                        ,[ParentProjectID]
                        ,[MinTenderAmount]
                        ,w.[ID] AS wid
                        ,w.[AssuranceProvider]
                        ,w.[AssuranceSituation]
                        ,w.FKRemark
                        ,r.[ID] AS rid
                        ,r.ContractAgreement
                        ,r.EFPhotographs
                        ,r.EFPhotoExplain
                        ,r.EFPhotoPath
                        ,r.TitlePic
                        ,[VerifyIdCard]
                          ,[VerifyIsMarriage]
                          ,[VerifyBooklets]
                          ,[VerifyHouseProperty]
                          ,[VerifyBankWater]
                          ,[VerifyRealEstateBook]
                          ,[VerifyCreditReporter]
                          ,[VerifyOthers]
                        FROM [T_Project] p  LEFT JOIN [T_WindControlInformation] w ON w.[ProjectID] = p.[ID] 
                        LEFT JOIN [T_RelevantDocument] r ON r.[ProjectID] = p.[ID]
                        WHERE p.[ID] = @ID";
            return base.Get<T_Project_EX>(sql, new { ID = id });
        }


        public List<T_RelevantDocument> GetRelevantList(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[ProjectID]
                          ,[ContractAgreement]
                          ,[EFPhotographs]
                          ,[EFPhotoExplain]
                          ,[EFPhotoPath]
                          ,[TitlePic]
                      FROM [T_RelevantDocument] WHERE [ProjectID] = @ID";
            return base.Query<T_RelevantDocument>(sql, new { ID = id });
        }

        public List<T_WindControlInformation> GetWindList(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[ProjectID]
                          ,[AssuranceProvider]
                          ,[AssuranceSituation]
                           ,FKRemark
                      FROM [T_WindControlInformation] WHERE [ProjectID] = @ID";
            return base.Query<T_WindControlInformation>(sql, new { ID = id });
        }


        public T_Project_EX GetProject_EXByID(int? id)
        {
            string sql = @" SELECT e.[ID]
                          ,e.[EnterpriseName]
                          ,e.[LegalRepresentative]
                          ,DATEDIFF(YEAR,e.[RegistedDate],GETDATE()) AS Life
                          ,e.[RegistedCapital]
                          ,e.[AssetAmt]
                          ,e.[OperatingCashflow]
                          ,e.[Trade]
                          ,e.[BusinessCircumstance]
                          ,e.[LitigationSituation]
                          ,e.[Remarks]
                          ,p.[ID]
                          ,p.[ProjectNo]
                          ,p.[ProjectName]
                          ,p.[ProjectContent]
                          ,p.[TenderStatus]
                          ,CONVERT(DECIMAL(18,2),(p.[TotalLoan])) AS TotalLoan
                          ,p.[AnnualisedRates]*100 AS AnnualisedRates
                          ,p.[AnnualisedRates]+ISNULL(p.[AddAnnualisedRates],0) as Annualised
                          ,p.[AddAnnualisedRates]*100 AS AddAnnualisedRates
                          ,p.[AddAnnualisedRates] as AddAnnualised
                          ,p.[EnterpriseID]
                          ,p.[EnterpriseAgent]
                          ,p.[ProjectLocation]
                          ,p.[ProjectDetail]
                          ,CASE WHEN datediff(DAY, GETDATE(),[RepaymentPlanDate]) < 0 THEN 0 ELSE datediff(DAY, GETDATE(),[RepaymentPlanDate]) END AS DatediffDate
                          ,p.[LoanPurpose]
                          ,p.[RepaymentSource]
                          ,p.[LoanType]
                          ,p.[AuditStatus]
                          ,p.[LoanStatus]
                          ,p.[RepaymentStatus]
                          ,p.[CreateTime]
                          ,p.[CreateUser]
                          ,p.[AuditTime]
                          ,p.[AuditUser]
                          ,p.[LoanTime]
                          ,p.[LoanUser]
                          ,p.[RepaymentTime]
                          ,p.[RepaymentUser]
                         ,P.ProjectDuration
                          ,p.[DurationMonthOrDay]
                         ,p.ProjectDetail
                        ,p.[MinTenderAmount]
,CONVERT(char(10),p.[CreateTime],120) AS CreateTimeStr
, CONVERT(char(10),p.[AuditTime],120) AS AuditTimeStr
, CONVERT(char(10),p.[LoanTime],120) AS LoanTimeStr
, CONVERT(char(10),p.[RepaymentTime],120) AS RepaymentTimeStr
,    case 
               	when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 1
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 2
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 3
                when A.RepaymentStatus = 1 
                then 4
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 5  
                
                else 6
                end as ProjectStatus
                      FROM [T_Project] p LEFT JOIN [T_Enterprise] e ON p.[EnterpriseID]=e.[ID] WHERE p.[ID]=@ID";
            return base.Get<T_Project_EX>(sql, new { ID = id });
        }

        /// <summary>
        ///项目已投金额 
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public T_Investment_EX GetInvesmentAmt(int? projectId)
        {
            string sql = @"SELECT
t.*,
convert(varchar, cast(round(t.TotalTenderAmt,2) as money),1) as TotalTenderAmtStr,
convert(varchar, cast(round(t.TotalLoan,2) as money),1) as TotalLoanStr,
convert(varchar, cast(round(t.BalanceTenderAmt,2) as money),1) as BalanceTenderAmtStr
from
(
SELECT
  t.TotalTenderAmt,CONVERT(INT,(T.TotalTenderAmt/(case T.TotalLoan when 0 then 1 else T.TotalLoan end))*100) AS Rates
                            ,T.TotalLoan,(T.TotalLoan-T.TotalTenderAmt) AS BalanceTenderAmt
                             FROM 
                            (
                            SELECT   CONVERT(DECIMAL(18,2),(ISNULL(SUM(B.TenderAmt),0)))   TotalTenderAmt,
                            (select CONVERT(DECIMAL(18,2),TotalLoan) from T_Project where ID=@ID) AS TotalLoan
                             FROM T_Project A JOIN  T_Investment B ON A.ID=B.ProjectId 
                            WHERE A.ID=@ID and B.Status=0 
                            )T 
                            
                            )t";
            return base.Get<T_Investment_EX>(sql, new { ID = projectId });
        }

        /// <summary>
        /// 项目还款计划列表
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public List<T_RepaymentPlan_EX> QueryRepPlanList(int? projectId)
        {
            string sql = @"SELECT [ID]
                          ,[RepaymentPlanNO]
                          ,[RepaymentPlanDate]
                          ,[CurrentPrincipal]
                          ,[RemainingPrincipal]
                            ,Status
                          ,CASE [Status] WHEN 1 THEN '未还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,[ProjectID] FROM T_RepaymentPlan WHERE [ProjectID]=@ProjectID ORDER BY ID ASC";
            return base.Query<T_RepaymentPlan_EX>(sql, new { ProjectID = projectId });
        }

        /// <summary>
        /// 根据ID生成项目收益日期
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateRepPlanDate(int? id, DateTime planDate, int status)
        {
            string sql = "UPDATE T_RepaymentPlan SET RepaymentPlanDate=@RepaymentPlanDate,Status=@Status WHERE ID=@ID";
            return base.Excute(sql, new { RepaymentPlanDate = planDate, ID = id, Status = status });
        }

        /// <summary>
        /// 更新项目状态为已满标
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateProTendStatus(int? id)
        {
            string sql = " UPDATE T_Project SET TenderStatus=1 WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }

        //根据projectNo查询这个标的Id
        public int SelectIdByProjectNo(string projectNo)
        {
            string sql = "select [ID] from [T_Project] where ProjectNo=@ProjectNo";
            return base.Get<int>(sql, new { ProjectNo = projectNo });
        }

        //根据T_RepaymentPlan 表的Id 查询出剩余的金额，是否要还本金
        public T_RepaymentPlan_EX GetRepaymentPlanById(int id)
        {
            string sql = @"SELECT [RepaymentPlanNO],[RepaymentPlanDate],[CurrentPrincipal],[RemainingPrincipal],[Status],[ProjectID] 
FROM [T_RepaymentPlan] where ID=@ID";
            return base.Get<T_RepaymentPlan_EX>(sql, new { ID = id });
        }

        //查询出子项目集合
        public List<T_Project> QueryChildProjectList(int parentProjectId)
        {
            string sql = @"select ID, ProjectNo, ProjectName, ProjectContent, TotalLoan, AnnualisedRates, AddAnnualisedRates, EnterpriseID, EnterpriseAgent
, ProjectLocation, RepaymentPlanDate, LoanPurpose, RepaymentSource, LoanType, AuditStatus, LoanStatus, RepaymentStatus, CreateTime, CreateUser
, AuditTime, AuditUser, LoanTime, LoanUser, RepaymentTime, RepaymentUser, ProjectDuration, DurationMonthOrDay, AuditExplain, TenderStatus, SubProject
, EachSubProjectAmount, ParentProjectID, MinTenderAmount, ProjectBeginDate, ProjectEndDate, ProjectDetail, IsNewProject, TenderTime, RepaymentType, VerifyIdCard
, VerifyIsMarriage, VerifyBooklets, VerifyHouseProperty, VerifyBankWater, VerifyRealEstateBook, VerifyCreditReporter, VerifyOthers
                           from T_Project where ParentProjectID = @ParentProjectID";
            return base.Query<T_Project>(sql, new { ParentProjectID = parentProjectId });
        }

        //查询出一个实体对象
        public T_Project GetModel(int id)
        {
            string sql = @"select ID, ProjectNo, ProjectName, ProjectContent, TotalLoan, AnnualisedRates, AddAnnualisedRates, EnterpriseID, EnterpriseAgent
, ProjectLocation, RepaymentPlanDate, LoanPurpose, RepaymentSource, LoanType, AuditStatus, LoanStatus, RepaymentStatus, CreateTime, CreateUser
, AuditTime, AuditUser, LoanTime, LoanUser, RepaymentTime, RepaymentUser, ProjectDuration, DurationMonthOrDay, AuditExplain, TenderStatus, SubProject
, EachSubProjectAmount, ParentProjectID, MinTenderAmount, ProjectBeginDate, ProjectEndDate, ProjectDetail, IsNewProject, TenderTime, RepaymentType, VerifyIdCard
, VerifyIsMarriage, VerifyBooklets, VerifyHouseProperty, VerifyBankWater, VerifyRealEstateBook, VerifyCreditReporter, VerifyOthers
                           from T_Project where ID = @ID";
            return base.Get<T_Project>(sql, new { ID = id });
        }

        //查询出还款计划拓展集合
        public List<T_RepaymentPlan_EX> QueryRepaymentPlanList(int projectId)
        {
            string sql = @"SELECT [ID]
                          ,[RepaymentPlanNO]
                          ,[RepaymentPlanDate]
                          ,[CurrentPrincipal]
                          ,[RemainingPrincipal]
                            ,Status
                          ,CASE [Status] WHEN 1 THEN '未还款' WHEN 2 THEN '正在还款' WHEN 3 THEN '已还款'  END statusName
                          ,[ProjectID] FROM T_RepaymentPlan WHERE [ProjectID]=@ProjectID ORDER BY ID ASC";
            return base.Query<T_RepaymentPlan_EX>(sql, new { ProjectID = projectId });
        }

        //更新还款计划
        public int UpdateRepaymentPlanDate(int id, DateTime planDate, int status)
        {
            string sql = "UPDATE T_RepaymentPlan SET RepaymentPlanDate=@RepaymentPlanDate,Status=@Status WHERE ID=@ID";
            return base.Excute(sql, new { RepaymentPlanDate = planDate, ID = id, Status = status });
        }
        //获取还款对象
        public T_RepaymentPlan GetRepaymentPlan(int projectId)
        {
            string sql = @"select [ID]
                          ,[RepaymentPlanNO]
                          ,[RepaymentPlanDate]
                          ,[CurrentPrincipal]
                          ,[RemainingPrincipal]
                          ,[Status]
                          ,[ProjectID]
                           FROM [T_RepaymentPlan] where [ProjectID]=@ProjectID";
            return base.Get<T_RepaymentPlan>(sql, new { ProjectID = projectId });
        }
        //根据标号来查询实体
        public T_Project GetModelByProjectNo(string projectNo)
        {
            string sql = @"select ID, ProjectNo, ProjectName, ProjectContent, TotalLoan, AnnualisedRates, AddAnnualisedRates, EnterpriseID, EnterpriseAgent
, ProjectLocation, RepaymentPlanDate, LoanPurpose, RepaymentSource, LoanType, AuditStatus, LoanStatus, RepaymentStatus, CreateTime, CreateUser
, AuditTime, AuditUser, LoanTime, LoanUser, RepaymentTime, RepaymentUser, ProjectDuration, DurationMonthOrDay, AuditExplain, TenderStatus, SubProject
, EachSubProjectAmount, ParentProjectID, MinTenderAmount, ProjectBeginDate, ProjectEndDate, ProjectDetail, IsNewProject, TenderTime, RepaymentType, VerifyIdCard
, VerifyIsMarriage, VerifyBooklets, VerifyHouseProperty, VerifyBankWater, VerifyRealEstateBook, VerifyCreditReporter, VerifyOthers
                           from T_Project where ProjectNo = @ProjectNo";
            return base.Get<T_Project>(sql, new { ProjectNo = projectNo });
        }

        public PagedList<T_Project_EX> QueryOverFlowProjectList(ProjectSearch search, PageView view)
        {
            string cols = @" ID,ProjectNo,ProjectName,TotalLoan,myinvestment.TenderSum as TenderSum ";
            string sqlWhere = @"AND p.TotalLoan<myinvestment.TenderSum";


            //return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Sequence]", "");
            return base.PageGet<T_Project_EX>(view, cols, @" T_Project p left join 
( select SUM(TenderAmt) as TenderSum,ProjectId from T_Investment where Status=1 group by ProjectId ) myinvestment 

on p.ID=myinvestment.ProjectId ", sqlWhere, " Id", "");

        }


    }
}
